﻿Imports System.Data.SqlClient
Public Class consultas

    Dim ad As New AccesoDatos()
    Dim query As String
    Dim tabla As Data.DataTable

    '--------Insert

    Public Function InsertJuez(ByRef juez As Juez) As Integer
        query = "insert into juez (nombre, apellido, nro_documento, tipo_documento, fecha_nacimiento) values ('"
        query += juez.nombreInfo() & "','" & juez.apellidoInfo() & "'," & juez.nroDocInfo() & ",'"
        query += juez.tipoDocInfo & "','" & juez.fechaNacInfo() & "');"

        ad.consultaNonQuery(query)
        Return 1

    End Function

    Public Function InsertClub(ByRef club As ClubBean) As Integer

        query = "insert into direccion (calle, nro, cod_postal, depto, piso) values ('" & club.domicilioInfo.calleInfo() & "', '" & club.domicilioInfo.numeroInfo()
        query += "', '" & club.domicilioInfo.codigoPostalInfo() & "', '" & club.domicilioInfo.deptoInfo() & "', '" & club.domicilioInfo.pisoInfo() & "');"
        ad.consultaNonQuery(query)

        query = "select id_direccion from direccion where calle = '" & club.domicilioInfo.calleInfo() & "' and nro = '" & club.domicilioInfo.numeroInfo()
        query += "' and cod_postal = '" & club.domicilioInfo.codigoPostalInfo() & "' and depto = '" & club.domicilioInfo.deptoInfo() & "' and piso = '" & club.domicilioInfo.pisoInfo() & "';"


        Dim tablaTemp As Data.DataTable = ad.consultaReader(query)
        club.domicilioInfo.id_direccionInfo() = tablaTemp.Rows(0)(0)

        query = "insert into club (nombre, cuit, id_direccion, fecha_fundacion, estado_cancha, tipo_piso) values ('" & club.nombreInfo() & "', '" & club.cuitInfo()
        query += "', '" & club.domicilioInfo.id_direccionInfo & "', '" & club.fechaFundacion() & "', '" & club.estadoCanchaInfo() & "', '" & club.tipoPisoInfo() & "');"
        ad.consultaNonQuery(query)

        Return 1
    End Function

    Public Function InsertSocio(ByRef socio As Socio) As Integer
        query = "insert into direccion (calle, nro, cod_postal, depto, piso) values ('" & socio.domicilioInfo.calleInfo() & "', '" & socio.domicilioInfo.numeroInfo()
        query += "', '" & socio.domicilioInfo.codigoPostalInfo() & "', '" & socio.domicilioInfo.deptoInfo() & "', '" & socio.domicilioInfo.pisoInfo() & "');"
        ad.consultaNonQuery(query)

        query = "select id_direccion from direccion where calle = '" & socio.domicilioInfo.calleInfo() & "' and nro = '" & socio.domicilioInfo.numeroInfo()
        query += "' and cod_postal = '" & socio.domicilioInfo.codigoPostalInfo() & "' and depto = '" & socio.domicilioInfo.deptoInfo() & "' and piso = '" & socio.domicilioInfo.pisoInfo() & "';"


        Dim tablaTemp As Data.DataTable = ad.consultaReader(query)
        socio.domicilioInfo.id_direccionInfo() = tablaTemp.Rows(0)(0)

        query = "insert into socio (nro_documento, tipo_doc, id_club, nombre, apellido, fecha_nac, fecha_alta, es_jugador, id_direccion) values ('" & socio.nroDocInfo() & "', '"
        query += socio.tipoDocInfo() & "', '" & socio.clubInfo.idInfo() & "', '" & socio.nombreInfo() & "', '" & socio.apellidoInfo() & "', '" & socio.fechaNacInfo() & "', '"
        query += socio.fechaAltaInfo() & "', '" & socio.esJugadorInfo() & "', '" & socio.domicilioInfo.id_direccionInfo() & "');"
        ad.consultaNonQuery(query)

        Return 1
    End Function

    Public Function InsertProducto(ByRef producto As Producto) As Integer
        query = "insert into producto (nombre, precio) values  ('" & producto.nombreInfo() & "', '"
        query += producto.precioInfo() & "');"
        ad.consultaNonQuery(query)
        Return 1
    End Function

    '-----------Update

    Public Function ModificarJuez(ByRef juez As Juez) As Integer
        query = "update juez set nombre = '" & juez.nombreInfo & "', apellido = '" & juez.apellidoInfo
        query += "', nro_documento = '" & juez.nroDocInfo & "', tipo_documento = '" & juez.tipoDocInfo
        query += "', fecha_nacimiento = '" & juez.fechaNacInfo & "'"
        query += " where id_juez = " & juez.idInfo & ";"

        ad.consultaNonQuery(query)
        Return 1

    End Function

    Public Function ModificarClub(ByRef club As ClubBean) As Integer
        query = "update club set nombre = '" & club.nombreInfo & "', cuit = '" & club.cuitInfo & "', fecha_fundacion = '" & club.fechaFundacion & "', estado_cancha = '"
        query += club.estadoCanchaInfo & "', tipo_piso = '" & club.tipoPisoInfo & "' where id_club = '" & club.idInfo & "';"
        MsgBox(query)
        ad.consultaNonQuery(query)

        query = " update direccion set calle = '" & club.domicilioInfo.calleInfo & "', nro = '" & club.domicilioInfo.numeroInfo & "', cod_postal = '" & club.domicilioInfo.codigoPostalInfo
        query += "', depto = '" & club.domicilioInfo.deptoInfo & "', piso = '" & club.domicilioInfo.pisoInfo & "' where id_direccion = '" & club.domicilioInfo.id_direccionInfo & "'; "
        MsgBox(query)
        ad.consultaNonQuery(query)
        Return 1
    End Function

    Public Function ModificarSocio(ByRef socio As Socio) As Integer
        query = "update socio set apellido = '" & socio.apellidoInfo & "', nombre = '" & socio.nombreInfo & "', fecha_nac = '" & socio.fechaNacInfo
        query += "', fecha_alta = '" & socio.fechaAltaInfo & "', es_jugador = '" & socio.esJugadorInfo & ", "



        Return 1
    End Function

    Public Function ModificarProducto() As Integer
        Return 1
    End Function

    '-----------Delette

    Public Function Eliminar(ByVal tb As String, ByVal id As String) As Integer
        ad.consultaNonQuery("Delete from " & tb & " where id_" & tb & " = " & id)
        Return 1
    End Function

    '-----------Consultar

    Public Function Consultar(ByVal param As String) As Data.DataTable
        Return ad.consultaReader("select * from " & param)
    End Function

    Public Function ConsultarTablaXId(ByVal param As String, ByVal id As String) As Data.DataTable
        Return ad.consultaReader("select * from " & param & " where id_" & param & " = " & id & "; ")
    End Function

End Class
